/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.foment.gem;
import java.net.*;
import config.TransferProtocol;
import java.io.*;
import java.sql.SQLException;
import java.util.Enumeration;
import javax.sql.*;
import org.foment.data.*;

/**
 *Klasa ServerHandler predstavlja nit koja ce
 * obradjivati klijenta.
 * @author dusan
 */
public class ServerHandler extends Thread
{
    public ServerHandler(Socket s, int id)
    {
        client = s;
        connId = id;
        try
        {
            // otvaramo stream-ove za pisanje
            out = new ObjectOutputStream(client.getOutputStream());
            in = new ObjectInputStream(client.getInputStream());
        }
        catch(IOException e) {System.out.println("ServerHandler " +e.getMessage());}
        Server.connections.addElement(this); // dodajemo vezu u vektor svih veza.
        System.out.println("ServerHandler: Streams opened successful");
    }
    
    @Override
    public void run()
    {
        System.out.println("ServerHandler: Thread("+connId+") started!");
        // u petlji, SH cita komandu od klijenta i salje mu nazad rezultat
        // u zavisnosti od tipa poruke.
        while(true)
        {
            try
            {
                command = in.readShort();
                // ako je tip poruke SQL_QUERY, primamo upit u string. Izvrsavamo upit u bazi i saljemo nazad
                // ResultSet
                if (command == TransferProtocol.SQL_QUERY) {

                    String query = (String)in.readObject();
                    System.out.println("ServerHandler: recieved SQL_QUERY command: "+ query);
                    //synchronized (Server.rs)
                    {
                        Server.rs = Server.stat.executeQuery(query);
                        SqlDataResultSet data = new SqlDataResultSet(Server.rs);
                        send(TransferProtocol.SQL_DATA, data);
                    }
                }
                else if (command == TransferProtocol.USER_LOGIN) {

                    // ako nam jestigao zahtev za logovanjem, primamo korisnika i proveravamo
                    // je l' postoji u bazi.
                    User u = (User)in.readObject();
                    Server.rs = Server.stat.executeQuery("SELECT UserID, UserFullName from User where UserUsername = '"
                            + u.getUsername() + "' and UserPassword = '" + u.getPassword() + "'");
                    // rs.first se pomera na prvi red. ako nema redova, vratice false
                    if (Server.rs.first() == false)
                        send(TransferProtocol.PASSWORD_INCORRECT, 0);
                    else {

                        // uzimamo korisnika iz baze i saljemo nazad
                        User logged = new User();
                        //logged.fullName = Server.rs.getString("UserFullName");
                        send(TransferProtocol.PASSWORD_CORRECT, logged);
                    }
                }
                else if(command == TransferProtocol.CLI_SEND_TABLE_READ_DATA) {

                    // get sql query about reading data for the main table class and send back the results
                    try {

                       String query = (String) in.readObject();
                       

                       Server.rs = Server.stat.executeQuery(query);
                       send(TransferProtocol.SRV_SEND_TABLE_READ_DATA, new SqlDataResultSet(Server.rs));

                    } catch(IOException ioe) {
                        ioe.printStackTrace();
                    }

                } else if(command == TransferProtocol.CLI_SEND_EMPLOYEE_ROW_MODIFY) {

                    // get sql query about modifying data for the main table class and send everyone the results, except client itself
                    try {

                       String query = (String) in.readObject();
                       Server.stat.executeUpdate(query);
                       // broadcast to everyone
                       //send(TransferProtocol.SRV_SEND_TABLE_READ_DATA, new SqlDataResultSet(Server.rs));

                    } catch(IOException ioe) {
                        ioe.printStackTrace();
                    }

                } else if(command == TransferProtocol.CLI_SEND_EMPLOYEE_NEW_ID) {

                    try {

                       // this integer is for receiving message only, we dont use it for anything
                       Integer tmp = (Integer) in.readObject();
                       Server.rs = Server.stat.executeQuery("SELECT max(EmployeeID) + 1 as EmployeeID FROM Employee;");

                       int id = 0;
                       while (Server.rs.next()) {

                           id = Server.rs.getInt("EmployeeID");
                           break;
                       }

                       // insert blank row with this ID in the database
                       Server.stat.executeUpdate("INSERT INTO Employee (EmployeeID) VALUES("+ id +");");
                       send(TransferProtocol.SRV_SEND_EMPLOYEE_NEW_ID, new Integer(id));

                    } catch(IOException ioe) {
                        ioe.printStackTrace();
                    }

                } else if(command == TransferProtocol.CLI_SEND_EMPLOYEE_ROW_DELETE) {

                    // get sql query about modifying data for the main table class and send everyone the results, except client itself
                    try {

                       Integer empID = (Integer) in.readObject();
                       int employeeID = empID.intValue();

                       if (employeeID > 0) {

                           String query = "DELETE FROM EmployeeEducation WHERE EmployeeID = " + employeeID + ";";
                           Server.stat.executeUpdate(query);

                           query = "DELETE FROM EmployeeInsurance WHERE EmployeeID = " + employeeID + ";";
                           Server.stat.executeUpdate(query);

                           query = "DELETE FROM EmployeeJobType WHERE EmployeeID = " + employeeID + ";";
                           Server.stat.executeUpdate(query);

                           query = "DELETE FROM EmployeeVacation WHERE EmployeeID = " + employeeID + ";";
                           Server.stat.executeUpdate(query);

                           query = "DELETE FROM Employee WHERE EmployeeID = " + employeeID + ";";
                           Server.stat.executeUpdate(query);
                       }

                       // broadcast to everyone
                       //send(TransferProtocol.SRV_SEND_TABLE_READ_DATA, new SqlDataResultSet(Server.rs));

                    } catch(IOException ioe) {
                        ioe.printStackTrace();
                    }

                } else if (command == TransferProtocol.CLI_SEND_SQLLISTBOX_QUERY) {

                    SqlComponentMessage msg = (SqlComponentMessage) in.readObject();

                    Server.rs = Server.stat.executeQuery(msg.getQuery());
                    SqlDataResultSet data = new SqlDataResultSet(Server.rs);

                    // we are now setting unique component id, so we will know which
                    // component will receive our response
                    data.setComponentUniqueID(msg.getComponentID());
                    send(TransferProtocol.SRV_SEND_SQLLISTBOX_QUERY, data);

                } else if (command == TransferProtocol.CLI_SEND_SQLCOMBOBOX_QUERY) {

                    SqlComponentMessage msg = (SqlComponentMessage) in.readObject();

                    Server.rs = Server.stat.executeQuery(msg.getQuery());
                    SqlDataResultSet data = new SqlDataResultSet(Server.rs);

                    // we are now setting unique component id, so we will know which
                    // component will receive our response
                    data.setComponentUniqueID(msg.getComponentID());
                    send(TransferProtocol.SRV_SEND_SQLCOMBOBOX_QUERY, data);

                } else if (command == TransferProtocol.CLI_SEND_LOADEMPLOYEES_QUERY) {

                    String query = (String) in.readObject();
                    Server.rs = Server.stat.executeQuery(query);
                    SqlDataResultSet data = new SqlDataResultSet(Server.rs);
                    send(TransferProtocol.SRV_SEND_LOADEMPLOYEES_QUERY, data);

                } else if (command == TransferProtocol.CLI_SEND_LISTPOSITIONCHANGE_QUERY) {

                    String query = (String) in.readObject();
                    Server.rs = Server.stat.executeQuery(query);
                    SqlDataResultSet data = new SqlDataResultSet(Server.rs);
                    send(TransferProtocol.SRV_SEND_LISTPOSITIONCHANGE_QUERY, data);

                } else if (command == TransferProtocol.CLI_SEND_MODIFYPOSITION_01_QUERY) {

                    String query = (String) in.readObject();
                    Server.rs = Server.stat.executeQuery(query);
                    SqlDataResultSet data = new SqlDataResultSet(Server.rs);
                    send(TransferProtocol.SRV_SEND_MODIFYPOSITION_01_QUERY, data);

                } else if (command == TransferProtocol.CLI_SEND_MODIFYPOSITION_02_QUERY) {

                    String query = (String) in.readObject();
                    Server.stat.executeUpdate(query);

                    // server just sends notification back to the client, that row is updated
                    send(TransferProtocol.SRV_SEND_MODIFYPOSITION_02_QUERY, new String(""));

                } else if (command == TransferProtocol.CLI_SEND_NEWPOSITION_01_QUERY) {

                    String query = (String) in.readObject();
                    Server.rs = Server.stat.executeQuery(query);
                    SqlDataResultSet data = new SqlDataResultSet(Server.rs);
                    send(TransferProtocol.SRV_SEND_NEWPOSITION_01_QUERY, data);

                } else if (command == TransferProtocol.CLI_SEND_NEWPOSITION_02_QUERY) {

                    String query = (String) in.readObject();
                    System.err.println("new position: " + query);
                    Server.stat.executeUpdate(query);

                    // server just sends notification back to the client, that row is updated
                    send(TransferProtocol.SRV_SEND_NEWPOSITION_02_QUERY, new String(""));

                } else if (command == TransferProtocol.CLI_SEND_DELETEPOSITION_QUERY) {

                    String query = (String) in.readObject();
                    Server.stat.executeUpdate(query);

                    // server just sends notification back to the client, that row is updated
                    send(TransferProtocol.SRV_SEND_DELETEPOSITION_QUERY, new String(""));

                } else if (command == TransferProtocol.CLI_SEND_INSURANCE_SELECT) {

                    String query = (String) in.readObject();
                    Server.rs = Server.stat.executeQuery(query);
                    SqlDataResultSet data = new SqlDataResultSet(Server.rs);

                    send(TransferProtocol.SRV_SEND_INSURANCE_SELECT, data);

                } else if (command == TransferProtocol.CLI_SEND_INSURANCE_DELETE) {

                    String query = (String) in.readObject();
                    Server.stat.executeUpdate(query);

                    // broadcast notificatin to everyone
                    //send(TransferProtocol.SRV_SEND_INSURANCE_DELETE, new String(""));

                } else if (command == TransferProtocol.CLI_SEND_INSURANCE_INSERT) {

                    String query = (String) in.readObject();
                    Server.stat.executeUpdate(query);

                    // broadcast notificatin to everyone
                    //send(TransferProtocol.SRV_SEND_INSURANCE_INSERT, new String(""));
                }







            }
            // kad stigne EOF, znaci klijent je zatvorio stream, tj. vezu.
            catch (EOFException e) { 
                System.err.println("EOF Exception: " + e.getMessage());

                System.out.println("ServerHandler: last command: "+ command);
                

                closeConnection();
                break;
            }
            catch(IOException e) {System.err.println("ServerHandler.readCommandFromClient() IOException " + e.getMessage());}
            catch(SQLException e) {
                System.err.print("Command: "+ command);
                System.err.println("ServerHandler.readCommandFromClient() SQLException " + e.getMessage());
            }
            catch(ClassNotFoundException e) {System.err.println("ServerHandler.readCommandFromClient()" + e.getMessage());}
        }
    }
    // fukcija koja salje nazad klijentu prvo tip poruke, zatim samu poruku
    public void send(short command, Object message)
    {
        try
        {
            out.writeShort(command);
            out.flush();
            out.writeObject(message);
            out.flush();
        }
        catch(IOException e) {System.err.println("ServerHandler, send " + e.getMessage());}
    }
    
    void closeConnection()
    {
        try
        {
            in.close();
            out.close();
            client.close();
            Server.connections.remove(this);
            System.out.print("ServerHandler: Connection " + connId + " terminated");
        }
        catch(IOException e) {System.err.println("ServerHandler, closeConnection: IOException " +e.getMessage());}
    }
    // metoda broadcast, salje svim ostalim klijentima poruku...
    public void broadcast(short command, Object message)
    {
        Enumeration e = Server.connections.elements();
        while (e.hasMoreElements())
        {
            ServerHandler sh = (ServerHandler)e.nextElement();
            if (sh.connId != this.connId)
                sh.send(command, message);
        }
    }
    private int connId; // redni broj veze 
    Socket client; // Socket za komunikaciju
    ObjectOutputStream out; // Stream-ovi
    ObjectInputStream in;
    private short command;
}
